import ExcelJS from 'exceljs'
import { snakeCase, upperCase } from 'lodash'

import type { IStyleBase, ICellData, IBorderStyleData, IWorkbookData, IWorksheetData } from '@univerjs/presets'
import { LocaleType, BorderStyleTypes } from '@univerjs/presets'


interface CellStyle extends IStyleBase {
  it?: number;
  vt?: number;
  ht?: number;
  tb?: number;
  ti?: number;
}

interface MergeCoordinates {
  startRow: number;
  startCol: number;
  endRow: number;
  endCol: number;
}

export async function excelToWorkbook(file: File): Promise<IWorkbookData> {
  if (!file.name.endsWith('.xlsx')) {
    throw new Error('仅支持 .xlsx 格式文件');
  }

  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.load(await file.arrayBuffer());

  const workbookData: IWorkbookData = {
    id: `workbook-${Date.now()}`,
    name: workbook.title || 'Imported Workbook',
    appVersion: "0.5.1-nightly.202412041606",
    locale: LocaleType.ZH_CN,
    sheetOrder: [],
    sheets: {},
    styles: {},
  };

  const styleCache = new Map<string, string>();
  let styleIndex = 0;

  workbook.eachSheet((worksheet, sheetId) => {
    const sheetData: IWorksheetData = {
      id: sheetId.toString(),
      name: worksheet.name,
      cellData: {},
      mergeData: [],
      rowData: {},
      columnData: {},
      rowCount: 0,
      columnCount: 0
    };

    // 动态计算行列数
    let maxRow = 0;
    let maxCol = 0;

    worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
      maxRow = Math.max(maxRow, rowNumber);
      row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
        maxCol = Math.max(maxCol, colNumber);
      });
    });

    sheetData.rowCount = maxRow || 200;
    sheetData.columnCount = maxCol || 30;

    // 处理列宽
    worksheet.columns?.forEach((col, index) => {
      sheetData.columnData[index] = {
        w: Math.round((col.width || 10) * 7 + 5),
        hd: col.hidden ? 1 : 0
      };
    });

    // 处理行高和单元格数据
    worksheet.eachRow((row, rowNumber) => {
      sheetData.rowData[rowNumber - 1] = {
        h: Math.round((row.height || 15) / 0.75)
      };

      row.eachCell((cell: ExcelJS.Cell, colNumber) => {
        const cellStyle = extractCellStyle(cell);
        const styleKey = JSON.stringify(cellStyle);

        if (!styleCache.has(styleKey)) {
          styleCache.set(styleKey, styleIndex.toString());
          workbookData.styles[styleIndex.toString()] = cellStyle;
          styleIndex++;
        }

        const cellData: ICellData = {
          v: cell.text ?? '',
          s: styleCache.get(styleKey)!
        };

        if (!sheetData.cellData[rowNumber - 1]) {
          sheetData.cellData[rowNumber - 1] = {};
        }
        sheetData.cellData[rowNumber - 1][colNumber - 1] = cellData;
      });
    });

    // 修改后的合并单元格处理代码
    if (worksheet.model.merges) {
      worksheet.model.merges.forEach(mergeStr => {
        try {
          const { startRow, startCol, endRow, endCol } = parseMergeString(mergeStr);

          // 动态扩展行列数（重要！）
          sheetData.rowCount = Math.max(sheetData.rowCount, endRow + 1);
          sheetData.columnCount = Math.max(sheetData.columnCount, endCol + 1);

          sheetData.mergeData.push({
            startRow,
            startColumn: startCol,
            endRow,
            endColumn: endCol
          });
        } catch (e) {
          console.warn('Invalid merge string:', mergeStr);
        }
      });
    }

    workbookData.sheetOrder.push(sheetId.toString());
    workbookData.sheets[sheetId.toString()] = sheetData;
  });

  return workbookData;
}

// 样式提取函数
function extractCellStyle(cell: ExcelJS.Cell): CellStyle {
  const style: CellStyle = {};

  // 字体样式
  if (cell.font) {
    style.ff = cell.font.name || '宋体';
    style.fs = cell.font.size || 10;
    style.bl = cell.font.bold ? 1 : 0;
    style.it = cell.font.italic ? 1 : 0;
    if (cell.font.underline) style.ul = { s: 1 };
    if (cell.font.color) {
      style.cl = { rgb: parseArgb(cell.font.color.argb) || '#000000' };
    }
  }

  // 对齐方式
  if (cell.alignment) {
    style.vt = mapVerticalAlign(cell.alignment.vertical);
    style.ht = mapHorizontalAlign(cell.alignment.horizontal);
    style.tb = cell.alignment.wrapText ? 3 : 0;
    style.ti = cell.alignment.indent;
  }

  // 背景填充
  if (cell.fill?.type === 'pattern') {
    const color = cell.fill.fgColor || cell.fill.bgColor;
    if (color) {
      style.bg = { rgb: parseArgb(color.argb) || '#FFFFFF' };
    }
  }

  // 边框处理
  const border: Record<string, IBorderStyleData> = {};
  (['top', 'left', 'bottom', 'right'] as const).forEach(side => {
    const borderSide = cell.border?.[side];
    if (borderSide?.style && borderSide.style !== 'none') {
      border[side[0]] = {
        s: mapBorderStyle(borderSide.style),
        cl: { rgb: parseArgb(borderSide.color?.argb) || '#000000' }
      };
    }
  });

  if (Object.keys(border).length > 0) {
    style.bd = border;
  }

  return style;
}

// 辅助函数
function parseArgb(argb?: string): string | null {
  if (!argb) return null;
  return argb.length === 8 ? `#${argb.slice(2)}` : `#${argb}`;
}

function mapVerticalAlign(align?: ExcelJS.Alignment['vertical']): number {
  switch (align) {
    case 'top': return 1;
    case 'middle': return 2;
    case 'bottom': return 3;
    default: return 2;
  }
}

function mapHorizontalAlign(align?: ExcelJS.Alignment['horizontal']): number {
  switch (align) {
    case 'left': return 1;
    case 'center': return 2;
    case 'right': return 3;
    default: return 1;
  }
}

// 智能转换函数
function excelJsStyleToUniverEnum(style: string): keyof typeof BorderStyleTypes | undefined {
  // 两步转换：1. 驼峰转蛇形 2. 转大写
  // 示例: 'dashDot' → 'dash_dot' → 'DASH_DOT'
  const normalized = upperCase(snakeCase(style));
  return normalized as keyof typeof BorderStyleTypes;
}

// 优化后的映射函数
function mapBorderStyle(style?: ExcelJS.BorderStyle): BorderStyleTypes {
  if (!style) return BorderStyleTypes.NONE;

  // 特殊处理 medium 前缀简写
  const normalizedStyle = style.replace(/^medium(?!-)/, 'medium_');

  const enumKey = excelJsStyleToUniverEnum(normalizedStyle);

  if (enumKey && BorderStyleTypes[enumKey] !== undefined) {
    return BorderStyleTypes[enumKey];
  }

  console.warn(`未映射的边框样式: ${style}`);
  return BorderStyleTypes.NONE;
}

function columnToIndex(column: string): number {
  return column
    .toUpperCase()
    .split('')
    .reduce((acc, char) => acc * 26 + (char.charCodeAt(0) - 64), 0) - 1;
}

function parseMergeString(range: string): MergeCoordinates {
  const [startStr, endStr] = range.split(':');

  const parseCell = (str: string) => {
    const parts = str.match(/^([A-Za-z]+)(\d+)$/i);
    if (!parts) throw new Error(`Invalid cell format: ${str}`);

    return {
      col: columnToIndex(parts[1]),
      row: parseInt(parts[2]) - 1
    };
  };

  const start = parseCell(startStr);
  const end = parseCell(endStr);

  return {
    startRow: start.row,
    startCol: start.col,
    endRow: end.row,
    endCol: end.col
  };
}
